﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;
using TSL_Tools.SqlTools;
using ReportingEngine;
//using ReportGenerator;
using System.IO;
using Xceed.Wpf.Toolkit.PropertyGrid;
using System.Globalization;
using System.Windows.Forms;

namespace VSLT_Reporting
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        private void ExitApplication()
        {
            this.Close();
        }

        private void SetTabAvailability(bool IsEnabled)
        {
            RibbonTabHome.IsEnabled = IsEnabled;
            RibbonTabTables.IsEnabled = IsEnabled;
            RibbonTabCharts.IsEnabled = IsEnabled;
            btnDatabaseInformation.IsEnabled = IsEnabled;
        }

        private void InstallSqlCodeOnDatabase()
        {
            //NOTE: Need to add message box and/or way of confirning that update needs to occur
            string resultsFromUpdate;
            TimeSpan ts = re.UpdateReportingEngine(out resultsFromUpdate);
            TextRange text = new TextRange(rtbResults.Document.ContentStart, rtbResults.Document.ContentEnd);
            text.Text = String.Format(Msg_DATABASEUPDATE_COMPLETED, resultsFromUpdate, ts.TotalSeconds);
        }

        private void LoadSampleTemplate()
        {
            OpenFileDialog dlg = new OpenFileDialog();
            if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                tbFileNameForTemplate.Text = dlg.FileName;
            }

            string str;
            string fileName = tbFileNameForTemplate.Text;

            using (StreamReader sr = new StreamReader(fileName))
            {
                str = sr.ReadToEnd();
            }
            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            MemoryStream ms = new MemoryStream(encoding.GetBytes(str));

            TextRange text = new TextRange(rtbResults.Document.ContentStart, rtbResults.Document.ContentEnd);

            if ((string)cbTemplateFileType.SelectionBoxItem == ".rtf")
                text.Load(ms, System.Windows.DataFormats.Rtf);
            else if ((string)cbTemplateFileType.SelectionBoxItem == ".xaml")
                text.Load(ms, System.Windows.DataFormats.Xaml);
            else
                System.Windows.MessageBox.Show(Msg_TSSL_ERROR_DATAFORMAT);
        }

        private void SaveSampleTemplate()
        {
            string fileName = tbFileNameForTemplate.Text;

            try
            {
                File.Delete(fileName);
            }
            catch (IOException ex)
            {
                System.Windows.MessageBox.Show(ex.Message);
            }

            using (FileStream fs = File.OpenWrite(fileName))
            {
                TextRange text = new TextRange(rtbResults.Document.ContentStart, rtbResults.Document.ContentEnd);
                if ((string)cbTemplateFileType.SelectionBoxItem == ".rtf")
                    text.Save(fs, System.Windows.DataFormats.Rtf);
                else if ((string)cbTemplateFileType.SelectionBoxItem == ".xaml")
                    text.Save(fs, System.Windows.DataFormats.Xaml);
                else
                    System.Windows.MessageBox.Show(Msg_FILEFORMATERROR);
            }
            tsslMessage.Content = String.Format(Msg_FILESAVED, fileName);
        }

        /// <summary>
        /// Gets the list of the top 5 tables from the DB, loads the topTables reporting template and populates the values into the template
        /// </summary>
        /// <param name="topTablesTemplate">location and name of the template file to use</param>
        /// <returns>the formatted template string</returns>
        private string BuildTopTablesDisplayString(string topTablesTemplate)
        {
            string topTables = "";
            using (StreamReader sr = new StreamReader(topTablesTemplate))
            {
                topTables = sr.ReadToEnd();
            }
            StringBuilder sb = new StringBuilder(topTables);

            string sqlConn = Properties.Settings.Default.sqlConn;
            string sqlCmd = String.Format(TSL_ReportingEngine.Sql_BIGGESTTABLESBYROWS);
            DataTable _table;
            TimeSpan ts = tts.ExecuteReaderTable(sqlCmd, out _table);
            AddDebugTimingInfo(ts, "--BuildTopTablesDisplayString  " + sqlCmd);
            mostRecentSqlQuery = sqlCmd;

            sb = sb.Replace("{{Table1}}", _table.Rows[0][0].ToString());
            sb = sb.Replace("{{Table2}}", _table.Rows[1][0].ToString());
            sb = sb.Replace("{{Table3}}", _table.Rows[2][0].ToString());
            sb = sb.Replace("{{Table4}}", _table.Rows[3][0].ToString());
            sb = sb.Replace("{{Table5}}", _table.Rows[4][0].ToString());

            sb = sb.Replace("{{Rowcount1}}", String.Format("{0:N0}",_table.Rows[0][1]));
            sb = sb.Replace("{{Rowcount2}}", String.Format("{0:N0}", _table.Rows[1][1]));
            sb = sb.Replace("{{Rowcount3}}", String.Format("{0:N0}", _table.Rows[2][1]));
            sb = sb.Replace("{{Rowcount4}}", String.Format("{0:N0}", _table.Rows[3][1]));
            sb = sb.Replace("{{Rowcount5}}", String.Format("{0:N0}", _table.Rows[4][1]));

            return sb.ToString();
        }

        /// <summary>
        /// Builds a report about the core database info
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="mainTemplateString"></param>
        /// <param name="fileRowTemplateString"></param>
        /// <param name="topTablesText"></param>
        private void BuildDatabaseInfoDisplayString(DataSet dataSet, string mainTemplateString, string fileRowTemplateString, string topTablesText)
        {
            #region -- Template File Replace Names and SPROC results table schemas
            /* 1) Version, Recovery Model, Num Cores, Memory in GB
    * 2) Drive Letter, MB free
    * 3) LoadTestDB stats (name, db_size, owner, dbid, created, status, compatibility_level)
    * 4) LoadTestDB File stats (name, fileid, filename, filegroup, size, maxsize, growth, usage)
    * 5) TempDB stats
    * 6) TempDB File stats
             
SQL Server Load Test DB Properties
	Machine and SQL information	
	Version	{{DBInfoVersion}}                   Table[0] Col[0]
	Recovery Model	{{DBInfoRecoveryModel}}     Table[0] Col[1]
	Number of cores	{{DBInfoNumCores}}          Table[0] Col[2]
	Memory (in GB)	{{BDInfoMemory}}            Table[0] Col[3]
	Drive Information	{{BDInfoDrives}}        Table[1], use stringbuilder to combine
	{{DatabaseName}} Information	            Properties.Settings.Default.
	Database Size	{{DBInfoDbSize}}            Table[2] Col[1]
	Owner	{{DBInfoOwner}}                     Table[2] Col[2]
	Database ID	{{DBInfoDBID}}                  Table[2] Col[3]
	Created On	{{DBInfoCreatedOn}}             Table[2] Col[4]
	Compatibility Level	{{DBInfoCompat}}        Table[2] Col[6]
	Status	                                    
	{{DBInfoStatus}}                            Table[2] Col[5] - Line separated on commas
	TempDB Information	
	Database Size	{{TempDBInfoDbSize}}        Table[4] Col[1]
	Owner	{{TempDBInfoOwner}}                 Table[4] Col[2]
	Database ID	{{TempDBInfoDBID}}              Table[4] Col[3]
	Created On	{{TempDBInfoCreatedOn}}         Table[4] Col[4]
	Compatibility Level	{{TempDBInfoCompat}}    Table[4] Col[6]
	Status	
	{{TempDBInfoStatus}}                        Table[4] Col[5] - Line separated on commas

Data File Properties
	{{DatabaseName}} Files

   {{DatabaseFileRows}} = Build a separate string using the DatabaseFileInfoLineTemplate, loading a copy for each row in Table[3]
    -----------------------------------------------------    
    {{DBInfoFileName}} = {{DBInfoFile}}
    FileId={{DBIfileId}}	FileGroup={{DBIfileGroup}}	FileSize={{DBIfileSize}}	MaxSize={{DBImaxSize}}	Growth={{Growth}}	Usage={{DBIusage}}                
    Table[3] columns = name, fileid, filename, filegroup, size, maxsize, growth, usage
   -----------------------------------------------------
   
          
   {{TempDbFileRows}} = Build a separate string using the DatabaseFileInfoLineTemplate, loading a copy for each row in Table[3]
    -----------------------------------------------------    
    {{DBInfoFileName}} = {{DBInfoFile}}
    FileId={{DBIfileId}}	FileGroup={{DBIfileGroup}}	FileSize={{DBIfileSize}}	MaxSize={{DBImaxSize}}	Growth={{Growth}}	Usage={{DBIusage}}                
    Table[5] columns = name, fileid, filename, filegroup, size, maxsize, growth, usage
   -----------------------------------------------------
 */
#endregion
            #region -- Sample Result Set
            /*  
             
============================================================================
Version	Recovery Model	Num Cores	Memory in GB	
100	SIMPLE	8	31	

============================================================================
drive	MB free	
C	609	
E	86461	

============================================================================
•	name	
•	db_size	
•	owner	
•	dbid	
•	created	
•	status	
•	compatibility_level	

•	LoadTest2010	   
•	9347.69 MB	
•	NORTHAMERICA\geoffgr	
•	5	
•	May  9 2013	
•	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled	
•	100	

============================================================================
•	name	
•	fileid	
•	filename	
•	filegroup	size	
•	maxsize	
•	growth	
•	usage	

•	LoadTest2010	
•	1	
•	C:\SQL Databases\LoadTest2010.mdf	
•	PRIMARY	
•	9571264 KB	
•	Unlimited	
•	1024 KB	
•	data only	

LoadTest2010_log	2	C:\SQL Databases\LoadTest2010_log.ldf		768 KB	2147483648 KB	10%	log only	

============================================================================
name	db_size	owner	dbid	created	status	compatibility_level	
tempdb	      8.50 MB	sa	2	Nov  5 2013	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics	100	

============================================================================
name	fileid	filename	filegroup	size	maxsize	growth	usage	
tempdev	1	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf	PRIMARY	8192 KB	Unlimited	10%	data only	
templog	2	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf		512 KB	Unlimited	10%	log only	

              
             */
            #endregion

            if (dataSet.Tables.Count == 6)
            {
                StringBuilder sb;
                StringBuilder tempSB = new StringBuilder();

                using (StreamReader sr = new StreamReader(mainTemplateString))
                {
                    sb = new StringBuilder(sr.ReadToEnd()); 
                }

                sb = sb.Replace("{{DatabaseName}}", Properties.Settings.Default.LoadTestDatabaseName);
                // Table 0
                sb = sb.Replace("{{DBInfoVersion}}", dataSet.Tables[0].Rows[0][0].ToString());
                sb = sb.Replace("{{DBInfoRecoveryModel}}", dataSet.Tables[0].Rows[0][1].ToString());
                sb = sb.Replace("{{DBInfoNumCores}}", dataSet.Tables[0].Rows[0][2].ToString());
                sb = sb.Replace("{{BDInfoMemory}}", dataSet.Tables[0].Rows[0][3].ToString());

                // Table 1
                for (int a = 0; a < dataSet.Tables[1].Rows.Count; a++)
                    tempSB.AppendLine(String.Format("{0}:\t{1} MB Free", dataSet.Tables[1].Rows[a][0], dataSet.Tables[1].Rows[a][1]));
                sb = sb.Replace("{{BDInfoDrives}}", tempSB.ToString()); //Combine

                // Table 2
                sb = sb.Replace("{{DBInfoDbSize}}", dataSet.Tables[2].Rows[0][1].ToString());
                sb = sb.Replace("{{DBInfoOwner}}", dataSet.Tables[2].Rows[0][2].ToString());
                sb = sb.Replace("{{DBInfoDBID}}", dataSet.Tables[2].Rows[0][3].ToString());
                sb = sb.Replace("{{DBInfoCreatedOn}}", dataSet.Tables[2].Rows[0][4].ToString());
                sb = sb.Replace("{{DBInfoCompat}}", dataSet.Tables[2].Rows[0][6].ToString());
                sb = sb.Replace("{{DBInfoStatus}}", dataSet.Tables[2].Rows[0][5].ToString().Replace(",","\r\n")); 

                // Table 4
                sb = sb.Replace("{{TempDBInfoDbSize}}", dataSet.Tables[4].Rows[0][1].ToString());
                sb = sb.Replace("{{TempDBInfoOwner}}", dataSet.Tables[4].Rows[0][2].ToString());
                sb = sb.Replace("{{TempDBInfoDBID}}", dataSet.Tables[4].Rows[0][3].ToString());
                sb = sb.Replace("{{TempDBInfoCreatedOn}}", dataSet.Tables[4].Rows[0][4].ToString());
                sb = sb.Replace("{{TempDBInfoCompat}}", dataSet.Tables[4].Rows[0][6].ToString());
                sb = sb.Replace("{{TempDBInfoStatus}}", dataSet.Tables[4].Rows[0][5].ToString().Replace(",", "\r\n"));

                string fileRowTemplate = "";
                using (StreamReader sr = new StreamReader(fileRowTemplateString))
                {
                    fileRowTemplate = sr.ReadToEnd();
                }
                // Table 3
                tempSB.Clear();
                for (int a = 0; a < dataSet.Tables[3].Rows.Count; a++)
                {
                    StringBuilder fileRow = new StringBuilder(fileRowTemplate);
                    fileRow = fileRow.Replace("{{DBInfoFileName}}", dataSet.Tables[3].Rows[a][0].ToString());
                    fileRow = fileRow.Replace("{{DBIfileId}}", dataSet.Tables[3].Rows[a][1].ToString());
                    fileRow = fileRow.Replace("{{DBInfoFile}}", dataSet.Tables[3].Rows[a][2].ToString());
                    fileRow = fileRow.Replace("{{DBIfileGroup}}", dataSet.Tables[3].Rows[a][3].ToString());
                    fileRow = fileRow.Replace("{{DBIfileSize}}", dataSet.Tables[3].Rows[a][4].ToString());                    
                    fileRow = fileRow.Replace("{{DBImaxSize}}", dataSet.Tables[3].Rows[a][5].ToString());
                    fileRow = fileRow.Replace("{{Growth}}", dataSet.Tables[3].Rows[a][6].ToString());
                    fileRow = fileRow.Replace("{{DBIusage}}", dataSet.Tables[3].Rows[a][7].ToString());
                    tempSB.Append(fileRow.ToString());
                }
                sb = sb.Replace("{{DatabaseFileRows}}",tempSB.ToString());

                // Table 5
                tempSB.Clear();
                for (int a = 0; a < dataSet.Tables[5].Rows.Count; a++)
                {
                    StringBuilder fileRow = new StringBuilder(fileRowTemplate);
                    fileRow = fileRow.Replace("{{DBInfoFileName}}", dataSet.Tables[5].Rows[a][0].ToString());
                    fileRow = fileRow.Replace("{{DBIfileId}}", dataSet.Tables[5].Rows[a][1].ToString());
                    fileRow = fileRow.Replace("{{DBInfoFile}}", dataSet.Tables[5].Rows[a][2].ToString());
                    fileRow = fileRow.Replace("{{DBIfileGroup}}", dataSet.Tables[5].Rows[a][3].ToString());
                    fileRow = fileRow.Replace("{{DBIfileSize}}", dataSet.Tables[5].Rows[a][4].ToString());                    
                    fileRow = fileRow.Replace("{{DBImaxSize}}", dataSet.Tables[5].Rows[a][5].ToString());
                    fileRow = fileRow.Replace("{{Growth}}", dataSet.Tables[5].Rows[a][6].ToString());
                    fileRow = fileRow.Replace("{{DBIusage}}", dataSet.Tables[5].Rows[a][7].ToString());
                    tempSB.Append(fileRow.ToString());
                }
                sb = sb.Replace("{{TempDbFileRows}}",tempSB.ToString());

                // Top 5 Tables by Size
                sb = sb.Replace("{{DbInfoTopTables}}", topTablesText);

                // Write the XAML out to the RichTextBox
                System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
                MemoryStream ms = new MemoryStream(encoding.GetBytes(sb.ToString()));

                TextRange text = new TextRange(rtbResults.Document.ContentStart, rtbResults.Document.ContentEnd);
                text.Load(ms, System.Windows.DataFormats.Xaml);
            }
            else
            {
                // Got a bad dataSet, so throw a warning and bail
                TextRange text = new TextRange(rtbResults.Document.ContentStart, rtbResults.Document.ContentEnd);
                text.Text = String.Format(Msg_ERROREXECUTINGDATABASEINFO, dataSet.Tables.Count);
            }
        }
    }
}



